﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FinanSoft.Entities;
using FinanSoft.Utilities;
using System.Data.SqlClient;
using FinanSoft.BusinessLayer;
using System.Drawing;

namespace FinanSoft.DataLayer
{
    class ItemDataManager
    {
        #region Fields
        /// <summary>
        /// Atributo privado del tipo de la clase. Unica instancia de esta clase que existira
        /// </summary>
        private static ItemDataManager _instance;
        #endregion

        #region Constructors
        /// <summary>
        /// Constructor privado de la clase para permitir la implementacion del patron singleton
        /// </summary>
        private ItemDataManager()
        {

        }
        #endregion

        #region Methods
        /// <summary>
        /// Metodo que obtienen la instancia existente de la clase. Si esta aun no ha sido instanciada, la instancia.
        /// </summary>
        public static ItemDataManager GetInstance()
        {
            if (_instance == null)
                _instance = new ItemDataManager();
            return _instance;
        }

        /// <summary>
        /// Cambia el costo unitario de un artículo en una bodega por un nuevo costo
        /// </summary>
        /// <param name="item"></param>
        /// <param name="store"></param>
        /// <param name="newCost"></param>
        /// <returns></returns>
        public bool UpdateUnitCost(Item item, StoreRoom store, double newCost)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = Constants.SP_NAME_UPDATE_UNIT_COST;

                SqlParameter[] parameters = {new SqlParameter(Constants.SP_PARAM_UPDATE_UNIT_COST_NEW_COST, newCost),
                                             new SqlParameter(Constants.SP_PARAM_UPDATE_UNIT_COST_ITEM_ID, item.Id),
                                             new SqlParameter(Constants.SP_PARAM_UPDATE_UNIT_COST_STORE_ROOM_ID, store.Id)};

                SqlDataHelper dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// Agrega la cantidad en stock dada al artículo para la bodega dada. La cantidad puede ser negativa.
        /// </summary>
        /// <param name="item"></param>
        /// <param name="store"></param>
        /// <param name="addedStockAmount"></param>
        /// <returns></returns>
        public bool UpdateStockAmount(Item item, StoreRoom store, int addedStockAmount)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = Constants.SP_NAME_UPDATE_STOCK_AMOUNT;

                SqlParameter[] parameters = {new SqlParameter(Constants.SP_PARAM_UPDATE_STOCK_AMOUNT_ADDED_STOCK, addedStockAmount),
                                             new SqlParameter(Constants.SP_PARAM_UPDATE_STOCK_AMOUNT_ITEM_ID, item.Id),
                                             new SqlParameter(Constants.SP_PARAM_UPDATE_STOCK_AMOUNT_STORE_ROOM_ID, store.Id)};

                SqlDataHelper dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// Obtiene la lista de unidades de medida
        /// </summary>
        /// <returns>El objeto lista de UM</returns>
        public List<MeasurementUnit> GetMeasuramentUnit()
        {
            List<MeasurementUnit> umList;

            try
            {
                umList = new List<MeasurementUnit>();
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = Constants.SP_NAME_GET_UM;
                SqlParameter[] parameters = new SqlParameter[0];
                SqlDataHelper dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                while (result.Read())
                {
                    int idUM = System.Convert.ToInt32(result[Constants.SP_COLUMN_GET_UM_ID]);
                    String name = (string)result[Constants.SP_COLUMN_GET_UM_NAME];
                    String acronimo = (string)result[Constants.SP_COLUMN_GET_UM_ACRONIMO];                
                    
                    umList.Add(new MeasurementUnit(idUM, name, acronimo));
                }
                dataHelper.CloseReader();
                return umList;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// Obtiene todas los articulos registradas en la base de datos y las retorna como una lista
        /// </summary>
        /// <returns>El objeto lista de articulos</returns>
        public List<Item> GetItems()
        {
            List<Item> articuloList;

            try
            {
                articuloList = new List<Item>();
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = Constants.SP_NAME_GET_ARTICLES;
                SqlParameter[] parameters = new SqlParameter[0];
                SqlDataHelper dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                while (result.Read())
                {
                    int id = System.Convert.ToInt32(result[Constants.SP_COLUMN_GET_ARTICLES_ID]);
                    String code = (string)result[Constants.SP_COLUMN_GET_ARTICLES_CODE];
                    String description = (string)result[Constants.SP_COLUMN_GET_ARTICLES_DESCRIPTION];
                    int idUnidadMedida = System.Convert.ToInt32(result[Constants.SP_COLUMN_GET_ARTICLES_ID_UNIDAD_MEDIDA]);
                    String coments = (string)result[Constants.SP_COLUMN_GET_ARTICLES_COMENT];
                    articuloList.Add(new Item(id, code, description, idUnidadMedida, coments, getStoreRoomInfo(id)));
                }
                dataHelper.CloseReader();
                return articuloList;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// Obtiene todas las imagenes de un articulo
        /// </summary>
        /// <returns>El objeto lista de articulos</returns>
        public List<Item> GetItemImages(int idItem)
        {
            List<Item> articuloList;

            try
            {
                articuloList = new List<Item>();
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = Constants.SP_NAME_GET_ITEM_IMAGE;
                SqlParameter[] parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter(Constants.SP_PARAM_GET_ITEM_IMAGE_CODE, idItem);
                SqlDataHelper dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                while (result.Read())
                {
                    int idFotoArt = System.Convert.ToInt32(result[Constants.SP_COLUMN_GET_ITEM_IMAGE_ID]);
                    byte[] itemImage = (byte[])result[Constants.SP_COLUMN_GET_ITEM_IMAGE_BYTES];
                    articuloList.Add(new Item(idFotoArt, itemImage));
                }
                dataHelper.CloseReader();
                return articuloList;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// Ingresa un nuevo item a la BD, crea el item en la tabla articulos y uno por bodega en la tabla bodegaArticulo
        /// toma las cuentas de la bodega por default
        /// </summary>
        /// <param name="newItem"> Recibe un objeto tipo articulo </param>
        /// <returns> retorna el id del articulo recien creado o -1 en caso de error </returns>
        public bool RegisterItemImage(Item newItem)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = Constants.SP_NAME_NEW_ITEM_IMAGE;

                SqlParameter[] parameters = {new SqlParameter(Constants.SP_PARAM_NEW_ITEM_IMAGE_CODE, newItem.Id),
                                             new SqlParameter(Constants.SP_PARAM_NEW_ITEM_IMAGE_BYTES, newItem.imagen),};

                SqlDataHelper dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);

                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// Obtiene los articulos de una bodega especifica de acuerdo a la base de datos
        /// </summary>
        /// <returns>El objeto lista de datos</returns>
        private List<ItemStoreRoomInfo> getStoreRoomInfo(int itemId)
        {
            List<ItemStoreRoomInfo> BodegaArticuloList;

            try
            {
                BodegaArticuloList = new List<ItemStoreRoomInfo>();
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = Constants.SP_NAME_GET_ARTICLES_BY_STORE;
                SqlParameter[] parameters = new SqlParameter[1];
                parameters[0] = new SqlParameter(Constants.SP_PARAM_ARTICLES_BY_STORE, itemId);
                SqlDataHelper dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                while (result.Read())
                {
                    int idBodega = System.Convert.ToInt32(result[Constants.SP_COLUMN_GET_ARTICLES_BY_STORE_ID_STORES]);
                    int idArticulo = System.Convert.ToInt32(result[Constants.SP_COLUMN_GET_ARTICLES_BY_STORE_ID_ITEM]);
                    int idCuentaInventario = System.Convert.ToInt32(result[Constants.SP_COLUMN_GET_ARTICLES_BY_STORE_ID_COUNT_INVENT]);
                    int idCuentaCostoVentas = System.Convert.ToInt32(result[Constants.SP_COLUMN_GET_ARTICLES_BY_STORE_ID_COUNT_COST_SALE]);
                    int idCuentaCompras = System.Convert.ToInt32(result[Constants.SP_COLUMN_GET_ARTICLES_BY_STORE_ID_COUNT_BUY]);
                    int idCuentaVentas = System.Convert.ToInt32(result[Constants.SP_COLUMN_GET_ARTICLES_BY_STORE_ID_COUNT_SALE]);
                    int stock = System.Convert.ToInt32(result[Constants.SP_COLUMN_GET_ARTICLES_BY_STORE_STOCK]);
                    int comprometido = System.Convert.ToInt32(result[Constants.SP_COLUMN_GET_ARTICLES_BY_STORE_COMMITED]);
                    int solicitado = System.Convert.ToInt32(result[Constants.SP_COLUMN_GET_ARTICLES_BY_STORE_REQUESTED]);
                    int costoUnitario = System.Convert.ToInt32(result[Constants.SP_COLUMN_GET_ARTICLES_BY_STORE_COST_UNIT]);
                    string descripcion = System.Convert.ToString(result[Constants.SP_COLUMN_GET_ARTICLES_BY_STORE_DESCRIPTION]);
                    string um = System.Convert.ToString(result[Constants.SP_COLUMN_GET_ARTICLES_BY_STORE_UM]);
                    string comentarios = System.Convert.ToString(result[Constants.SP_COLUMN_GET_ARTICLES_BY_STORE_COMENTS]);
                    string codItem = System.Convert.ToString(result[Constants.SP_COLUMN_GET_ARTICLES_BY_STORE_ITEM_CODE]);

                    BodegaArticuloList.Add(new ItemStoreRoomInfo(idBodega, idCuentaInventario, idCuentaCostoVentas, idCuentaVentas, idCuentaCompras, stock, comprometido, solicitado, costoUnitario));
                }
                dataHelper.CloseReader();
                return BodegaArticuloList;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        /// <summary>
        /// Ingresa un nuevo item a la BD, crea el item en la tabla articulos y uno por bodega en la tabla bodegaArticulo
        /// toma las cuentas de la bodega por default
        /// </summary>
        /// <param name="newItem"> Recibe un objeto tipo articulo </param>
        /// <returns> retorna el id del articulo recien creado o -1 en caso de error </returns>
        public int RegisterItem(Item newItem)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = Constants.SP_NAME_NEW_ITEM;

                SqlParameter[] parameters = {new SqlParameter(Constants.SP_PARAM_NEW_ITEM_CODE, newItem.CodArticulo),
                                             new SqlParameter(Constants.SP_PARAM_NEW_ITEM_DESCRIPTION, newItem.Descripción),
                                             new SqlParameter(Constants.SP_PARAM_NEW_ITEM_COMENT, newItem.Comentario),
                                             new SqlParameter(Constants.SP_PARAM_NEW_ITEM_UM_ID, newItem.UnidadMedida),};

                SqlDataHelper dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);

                if (result.HasRows)
                {
                    result.Read();
                    int idArticulo = Convert.ToInt32(result[Constants.SP_COLUMN_NEW_ITEM_ID]);
                    return idArticulo;
                }
                return -1;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }
        
        /// <summary>
        /// Actualiza un articulo de una bodega especifica, asi como sus datos default
        /// </summary>
        /// <param name="updatedItem"> contiene los valores para el articulo y para la bodega (cuentas)</param>
        /// <returns></returns>
        public bool UpdateItem(Item updatedItem, ItemStoreRoomInfo storeRoomInfo)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = Constants.SP_NAME_EDIT_ITEM;
                SqlParameter[] parameters = {new SqlParameter(Constants.SP_PARAM_EDIT_ITEM_ID, updatedItem.Id),
                                             new SqlParameter(Constants.SP_PARAM_EDIT_ITEM_CODE, updatedItem.CodArticulo),
                                             new SqlParameter(Constants.SP_PARAM_EDIT_ITEM_DESCRIPTION, updatedItem.Descripción),
                                             new SqlParameter(Constants.SP_PARAM_EDIT_ITEM_COMENT, updatedItem.Comentario),
                                             new SqlParameter(Constants.SP_PARAM_EDIT_ITEM_UM, updatedItem.UnidadMedida),
                                             new SqlParameter(Constants.SP_PARAM_EDIT_ITEM_SALES_ACCOUNT, storeRoomInfo.CuentaVentas.AccountID),
                                             new SqlParameter(Constants.SP_PARAM_EDIT_ITEM_PURCHASES_ACCOUNT, storeRoomInfo.CuentaCompras.AccountID),
                                             new SqlParameter(Constants.SP_PARAM_EDIT_ITEM_INVENTORY_ACCOUNT, storeRoomInfo.CuentaInventario.AccountID),
                                             new SqlParameter(Constants.SP_PARAM_EDIT_ITEM_SALES_COST_ACCOUNT, storeRoomInfo.CuentaCostoVentas.AccountID),
                                             new SqlParameter(Constants.SP_PARAM_EDIT_ITEM_STORE_ID, storeRoomInfo.StoreRoom.Id),};

                SqlDataHelper dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);
                return true;                
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        /// <summary>
        /// Ingresa un nuevo item a la BD, crea el item en la tabla articulos y uno por bodega en la tabla bodegaArticulo
        /// toma las cuentas de la bodega por default
        /// </summary>
        /// <param name="newItem"> Recibe un objeto tipo articulo </param>
        /// <returns> retorna el id del articulo recien creado o -1 en caso de error </returns>
        public bool DeleteItem(int idItem)
        {
            try
            {
                AppConfigReader appConfig = AppConfigReader.GetInstance();
                string spName = Constants.SP_NAME_DELETE_ITEM;

                SqlParameter[] parameters = {new SqlParameter(Constants.SP_PARAM_DELETE_ITEM_ID, idItem),};

                SqlDataHelper dataHelper = new SqlDataHelper(appConfig.GetConnectionString(Constants.DB_CONN));
                SqlDataReader result = dataHelper.ExecuteStoredProcedure(spName, parameters);

                return true;     
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        
        #endregion Methods
    }
}
